package br.unicentro.lynx.model.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import br.unicentro.lynx.model.factory.ConnectionFactory;
import br.unicentro.lynx.model.pojo.Loan;

public class LoanDao {
	private String sql;
	private Connection connection;
	private PreparedStatement statement;
	private ResultSet result;

	public void insert(Loan loan) {
		open();
		this.sql = "INSERT INTO Loans (id_book_loan, id_student_loan, date_loan_loan, date_return_loan) VALUES (?, ?, ?, ?)";
		try {
			this.statement = connection.prepareStatement(sql);
			this.statement.setInt(1, loan.getIdBookLoan());
			this.statement.setInt(2, loan.getIdStudentLoan());
			this.statement.setDate(3, loan.getDateLoanLoan());
			this.statement.setDate(4, loan.getDateReturnLoan());
			this.statement.execute();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			close();
		}
	}

	public void update(Loan loan) {
		open();
		this.sql = "UPDATE Loans SET id_book_loan = ?, id_student_loan = ?, date_loan_loan = ?, date_return_loan = ? WHERE id_loan = ?";
		try {
			this.statement = connection.prepareStatement(sql);
			this.statement.setInt(1, loan.getIdBookLoan());
			this.statement.setInt(2, loan.getIdStudentLoan());
			this.statement.setDate(3, loan.getDateLoanLoan());
			this.statement.setDate(4, loan.getDateReturnLoan());
			this.statement.setInt(5, loan.getIdLoan());
			this.statement.execute();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			close();
		}
	}

	public void delete(Loan loan) {
		open();
		this.sql = "DELETE FROM Loans WHERE id_loan = ?";
		try {
			this.statement = connection.prepareStatement(sql);
			this.statement.setInt(1, loan.getIdLoan());
			this.statement.execute();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			close();
		}
	}

	public Loan search(Integer id_loan) {
		open();
		this.sql = "SELECT * FROM Loans WHERE id_loan = ?";
		try {
			this.statement = connection.prepareStatement(sql);
			this.statement.setInt(1, id_loan);
			this.result = this.statement.executeQuery();
			if (this.result.next()) {
				return new Loan(result.getInt("id_loan"),
						result.getInt("id_book_loan"),
						result.getInt("id_student_loan"),
						result.getDate("date_loan_loan"),
						result.getDate("date_return_loan"));
			} else {
				return null;
			}
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			close();
		}
	}

	public ArrayList<Loan> list() {
		open();
		this.sql = "SELECT * FROM Loans";
		ArrayList<Loan> loans = new ArrayList<Loan>();
		try {
			this.statement = connection.prepareStatement(sql);
			this.result = this.statement.executeQuery();
			while (this.result.next()) {
				loans.add(new Loan(result.getInt("id_loan"), result
						.getInt("id_book_loan"), result
						.getInt("id_student_loan"), result
						.getDate("date_loan_loan"), result
						.getDate("date_return_loan")));
			}
			return loans;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			try {
				this.result.close();
				close();
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
		}
	}

	private void open() {
		this.connection = ConnectionFactory.getConnection(
				ConnectionFactory.URL, ConnectionFactory.USER,
				ConnectionFactory.PASSWORD);
	}

	private void close() {
		try {
			this.statement.close();
			this.connection.close();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
}